﻿<cfsilent>
	<cfscript>

		termId = event.getArg("TID");
		depId = event.getArg("DepID");

		academicYear = event.getArg("AcademicYear");
		academicTerm = event.getArg("AcademicTerm");
		
		sql = "SELECT 
					institute_name 
				FROM 
					t_institute 
				WHERE 
					institute_id = :instituteId ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="instituteId", value=depId, cfsqltype="cf_sql_varchar" );
		rs_departmentEntity = queryObj.execute( sql=sql ).getResult(); 
	
		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		downFile = URLEncodedFormat(rs_departmentEntity.institute_name & "-学期教学任务." & academicYear & "-" & academicTerm, "utf-8");
		
		/* 生成电子表格数据 */
		sql = "SELECT 
				  t_term.academic_year, t_term.academic_term, 
				  t_course.course_name, a.cidx, 
				  tch.tch_name, tch.tch_id, 
				  a.task_limite, a.task_chooesd, 
				  t_classroom_type.cr_type_name,
				  t_campus.campus_name, 
				  a.task_week, a.task_period, 
				  CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time, 
				  CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place, 
				  a.task_prop, 
				  t_course.course_code, 
				  t_course.period_theory, 
				  t_course.period_practice, 
				  t_course.period_computer, 
				  tch_sec.tch_name sec_name, tch_sec.tch_id sec_id, 
				  tch_ace.tch_name ace_name, tch_ace.tch_id ace_id, 
				  t_course.course_credit, 
				  a.task_classes 
				FROM 
				  t_task a 
				  INNER JOIN t_term ON t_term.term_id = a.term_id 
				  INNER JOIN t_course ON t_course.cid = a.cid 
				  INNER JOIN t_teacher tch ON tch.tch_id = a.tch_id 
				  INNER JOIN t_classroom_type ON t_classroom_type.cr_type = a.cr_type 
				  INNER JOIN t_campus ON t_campus.campus_id = a.campus_id 
				  INNER JOIN t_teacher tch_sec ON tch_sec.tch_id = a.tch_id_sec 
				  INNER JOIN t_teacher tch_ace ON tch_ace.tch_id = a.tch_id_ace 
				WHERE 
				  a.term_id = :termId 
				  AND 
				  t_course.institute_id = :depId 
				  AND 
				  SUBSTR(a.task_prop, 7, 1) = '0' 
				  AND 
				  EXISTS(
					   SELECT 1 FROM t_task_arrange b 
					   WHERE b.task_id = a.tsk_id 
							 AND 
							 b.term_id = :termId 
				  )
				ORDER BY 
				  t_course.course_name, a.cidx ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" );
		rs_planTask = queryObj.execute( sql=sql ).getResult(); 
													
		sql = "SELECT 
				  t_term.academic_year, t_term.academic_term, 
				  t_course.course_name, a.cidx, 
				  tch.tch_name, tch.tch_id, 
				  a.task_limite, a.task_chooesd, 
				  t_classroom_type.cr_type_name,
				  t_campus.campus_name, 
				  a.task_week, a.task_period, 
				  CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time, 
				  CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place, 
				  a.task_prop, 
				  t_course.course_code, 
				  t_course.period_theory, 
				  t_course.period_practice, 
				  t_course.period_computer, 
				  tch_sec.tch_name sec_name, tch_sec.tch_id sec_id, 
				  tch_ace.tch_name ace_name, tch_ace.tch_id ace_id, 
				  t_course.course_credit 
				FROM 
				  t_common_task a 
				  INNER JOIN t_term ON t_term.term_id = a.term_id 
				  INNER JOIN t_course ON t_course.cid = a.cid 
				  INNER JOIN t_teacher tch ON tch.tch_id = a.tch_id 
				  INNER JOIN t_classroom_type ON t_classroom_type.cr_type = a.cr_type 
				  INNER JOIN t_campus ON t_campus.campus_id = a.campus_id 
				  INNER JOIN t_teacher tch_sec ON tch_sec.tch_id = a.tch_id_sec 
				  INNER JOIN t_teacher tch_ace ON tch_ace.tch_id = a.tch_id_ace 
				WHERE 
				  a.term_id = :termId 
				  AND 
				  t_course.institute_id = :depId 
				  AND 
				  SUBSTR(a.task_prop, 7, 1) = '0' 
				  AND 
				  EXISTS(
					   SELECT 1 FROM t_task_arrange b 
					   WHERE b.task_id = a.tsk_id 
							 AND 
							 b.term_id = :termId 
				  )
				ORDER BY 
				  t_course.course_name, a.cidx ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" );
		rs_commonTask = queryObj.execute( sql=sql ).getResult(); 
				
		sql = "SELECT 
				  t_term.academic_year, t_term.academic_term, 
				  t_course.course_name, a.cidx, 
				  tch.tch_name, tch.tch_id, 
				  a.task_limite, a.task_chooesd, 
				  t_classroom_type.cr_type_name,
				  t_campus.campus_name, 
				  a.task_week, a.task_period, 
				  CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time, 
				  CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place, 
				  a.task_prop, 
				  t_course.course_code, 
				  t_course.period_theory, 
				  t_course.period_practice, 
				  t_course.period_computer, 
				  tch_sec.tch_name sec_name, tch_sec.tch_id sec_id, 
				  tch_ace.tch_name ace_name, tch_ace.tch_id ace_id, 
				  t_course.course_credit 
				FROM 
				  t_sport_task a 
				  INNER JOIN t_term ON t_term.term_id = a.term_id 
				  INNER JOIN t_course ON t_course.cid = a.cid 
				  INNER JOIN t_teacher tch ON tch.tch_id = a.tch_id 
				  INNER JOIN t_classroom_type ON t_classroom_type.cr_type = a.cr_type 
				  INNER JOIN t_campus ON t_campus.campus_id = a.campus_id 
				  INNER JOIN t_teacher tch_sec ON tch_sec.tch_id = a.tch_id_sec 
				  INNER JOIN t_teacher tch_ace ON tch_ace.tch_id = a.tch_id_ace 
				WHERE 
				  a.term_id = :termId 
				  AND 
				  t_course.institute_id = :depId 
				  AND 
				  SUBSTR(a.task_prop, 7, 1) = '0' 
				  AND 
				  EXISTS(
					   SELECT 1 FROM t_task_arrange b 
					   WHERE b.task_id = a.tsk_id 
							 AND 
							 b.term_id = :termId 
				  )
				ORDER BY 
				  t_course.course_name, a.cidx ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" );
		rs_sportTask = queryObj.execute( sql=sql ).getResult(); 
		
		sql = "SELECT 
				  t_term.academic_year, t_term.academic_term, 
				  t_course.course_name, a.cidx, 
				  tch.tch_name, tch.tch_id, 
				  a.task_limite, a.task_chooesd, 
				  t_classroom_type.cr_type_name,
				  t_campus.campus_name, 
				  a.task_week, a.task_period, 
				  CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time, 
				  CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place, 
				  a.task_prop, 
				  t_course.course_code, 
				  t_course.period_theory, 
				  t_course.period_practice, 
				  t_course.period_computer, 
				  tch_sec.tch_name sec_name, tch_sec.tch_id sec_id, 
				  tch_ace.tch_name ace_name, tch_ace.tch_id ace_id, 
				  t_course.course_credit 
				FROM 
				  t_reopen_task a 
				  INNER JOIN t_term ON t_term.term_id = a.term_id 
				  INNER JOIN t_course ON t_course.cid = a.cid 
				  INNER JOIN t_teacher tch ON tch.tch_id = a.tch_id 
				  INNER JOIN t_classroom_type ON t_classroom_type.cr_type = a.cr_type 
				  INNER JOIN t_campus ON t_campus.campus_id = a.campus_id 
				  INNER JOIN t_teacher tch_sec ON tch_sec.tch_id = a.tch_id_sec 
				  INNER JOIN t_teacher tch_ace ON tch_ace.tch_id = a.tch_id_ace 
				WHERE 
				  a.term_id = :termId 
				  AND 
				  t_course.institute_id = :depId 
				  AND 
				  SUBSTR(a.task_prop, 7, 1) = '0' 
				  AND 
				  EXISTS(
					   SELECT 1 FROM t_task_arrange b 
					   WHERE b.task_id = a.tsk_id 
							 AND 
							 b.term_id = :termId 
				  )
				ORDER BY 
				  t_course.course_name, a.cidx ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" );
		rs_openTask = queryObj.execute( sql=sql ).getResult(); 
		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("教学任务", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "课程类别,学年,学期,课程,学分,课序号,课程号,理论学时,实践学时,上机学时,主讲教师,职工号,第二主讲,职工号,助教,职工号,计划人数,实际人数,教室类别,排课校区,周次,周学时,上课时间,上课地点,周一,周二,周三,周四,周五,周六,周日,上课班级");
		
		row = 1;
		
		/* 写入计划任务 */
		for (j=1; j LTE rs_planTask.recordCount; j++){
			
			row++;
			
			rowData = "计划课程," 
						& rs_planTask["academic_year"][j] 
						& "," 
						& rs_planTask["academic_term"][j] 
						& "," 
						& rs_planTask["course_name"][j]
						& "," 
						& numberFormat( rs_planTask["course_credit"][j], "_.__" ) 
						& "," 
						& rs_planTask["cidx"][j] 
						& "," 
						& rs_planTask["course_code"][j] 
						& "," 
						& rs_planTask["period_theory"][j] 
						& "," 
						& rs_planTask["period_practice"][j] 
						& "," 
						& rs_planTask["period_computer"][j] 
						& "," 
						& rs_planTask["tch_name"][j] 
						& "," 
						& rs_planTask["tch_id"][j] 
						& ", , , , ," 
						& rs_planTask["task_limite"][j] 
						& "," 
						& rs_planTask["task_chooesd"][j] 
						& "," 
						& rs_planTask["cr_type_name"][j] 
						& "," 
						& rs_planTask["campus_name"][j] 
						& "," 
						& rs_planTask["task_week"][j] 
						& "," 
						& rs_planTask["task_period"][j]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_planTask["task_time"][j]), ",", "、", "all") 
						& "," 
						& replace(rs_planTask["task_place"][j], ",", "、", "all")
						& ", , , , , , , "
						& "," 
						& replace(rs_planTask["task_classes"][j], ",", "，", "all");
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_planTask["tch_id"][j] neq rs_planTask["sec_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["sec_name"][j], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["sec_id"][j], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_planTask["tch_id"][j] neq rs_planTask["ace_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["ace_name"][j], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["ace_id"][j], row, 16);
			}
			
			if (rs_planTask["task_time"][j] neq "-") {
				
				for (i=1; i lte listLen(rs_planTask["task_time"][j], ","); i++) {
					
					timeCell = listGetAt(rs_planTask["task_time"][j], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 24 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_planTask["task_place"][j] neq "-" and listLen(rs_planTask["task_place"][j], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_planTask["task_place"][j], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入通选课任务 */
		for (j=1; j LTE rs_commonTask.recordCount; j++){	
			row++;
			
			rowData = "全校通选课," 
						& rs_commonTask["academic_year"][j]
						& "," 
						& rs_commonTask["academic_term"][j] 
						& "," 
						& rs_commonTask["course_name"][j] 
						& "," 
						& numberFormat( rs_commonTask["course_credit"][j], "_.__" ) 
						& "," 
						& rs_commonTask["cidx"][j] 
						& "," 
						& rs_commonTask["course_code"][j] 
						& "," 
						& rs_commonTask["period_theory"][j] 
						& "," 
						& rs_commonTask["period_practice"][j] 
						& "," 
						& rs_commonTask["period_computer"][j]
						& "," 
						& rs_commonTask["tch_name"][j] 
						& "," 
						& rs_commonTask["tch_id"][j] 
						& ", , , , ," 
						& rs_commonTask["task_limite"][j] 
						& "," 
						& rs_commonTask["task_chooesd"][j] 
						& "," 
						& rs_commonTask["cr_type_name"][j] 
						& "," 
						& rs_commonTask["campus_name"][j] 
						& "," 
						& rs_commonTask["task_week"][j] 
						& "," 
						& rs_commonTask["task_period"][j]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_commonTask["task_time"][j]), ",", "、", "all") 
						& "," 
						& replace(rs_commonTask["task_place"][j], ",", "、", "all")
						& ", , , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_commonTask["tch_id"][j] neq rs_commonTask["sec_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["sec_name"][j], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["sec_id"][j], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_commonTask["tch_id"][j] neq rs_commonTask["ace_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["ace_name"][j], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["ace_id"][j], row, 16);
			}

			if (rs_commonTask["task_time"][j] neq "-") {
				
				for (i=1; i lte listLen(rs_commonTask["task_time"][j], ","); i++) {
					
					timeCell = listGetAt(rs_commonTask["task_time"][j], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 24 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_commonTask["task_place"][j] neq "-" and listLen(rs_commonTask["task_place"][j], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_commonTask["task_place"][j], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入体育项目课 */
		for (j=1; j LTE rs_sportTask.recordCount; j++){
			row++;
			
			rowData = "体育项目," 
						& rs_sportTask["academic_year"][j]
						& "," 
						& rs_sportTask["academic_term"][j] 
						& "," 
						& rs_sportTask["course_name"][j] 
						& "," 
						& numberFormat( rs_sportTask["course_credit"][j], "_.__" ) 
						& "," 
						& rs_sportTask["cidx"][j] 
						& "," 
						& rs_sportTask["course_code"][j] 
						& "," 
						& rs_sportTask["period_theory"][j] 
						& "," 
						& rs_sportTask["period_practice"][j] 
						& "," 
						& rs_sportTask["period_computer"][j]
						& "," 
						& rs_sportTask["tch_name"][j] 
						& "," 
						& rs_sportTask["tch_id"][j] 
						& ", , , , ," 
						& rs_sportTask["task_limite"][j] 
						& "," 
						& rs_sportTask["task_chooesd"][j] 
						& "," 
						& rs_sportTask["cr_type_name"][j] 
						& "," 
						& rs_sportTask["campus_name"][j] 
						& "," 
						& rs_sportTask["task_week"][j] 
						& "," 
						& rs_sportTask["task_period"][j]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_sportTask["task_time"][j]), ",", "、", "all") 
						& "," 
						& replace(rs_sportTask["task_place"][j], ",", "、", "all")
						& ", , , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_sportTask["tch_id"][j] neq rs_sportTask["sec_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["sec_name"][j], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["sec_id"][j], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_sportTask["tch_id"][j] neq rs_sportTask["ace_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["ace_name"][j], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["ace_id"][j], row, 16);
			}

			if (rs_sportTask["task_time"][j] neq "-") {
				
				for (i=1; i lte listLen(rs_sportTask["task_time"][j], ","); i++) {
					
					timeCell = listGetAt(rs_sportTask["task_time"][j], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 24 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_sportTask["task_place"][j] neq "-" and listLen(rs_sportTask["task_place"][j], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_sportTask["task_place"][j], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入单开班任务 */
		for (j=1; j LTE rs_openTask.recordCount; j++){	
			row++;
			
			rowData = "单开班任务," 
						& rs_openTask["academic_year"][j]
						& "," 
						& rs_openTask["academic_term"][j] 
						& "," 
						& rs_openTask["course_name"][j] 
						& "," 
						& numberFormat( rs_openTask["course_credit"][j], "_.__" ) 
						& "," 
						& rs_openTask["cidx"][j] 
						& "," 
						& rs_openTask["course_code"][j] 
						& "," 
						& rs_openTask["period_theory"][j] 
						& "," 
						& rs_openTask["period_practice"][j] 
						& "," 
						& rs_openTask["period_computer"][j]
						& "," 
						& rs_openTask["tch_name"][j] 
						& "," 
						& rs_openTask["tch_id"][j] 
						& ", , , , ," 
						& rs_openTask["task_limite"][j] 
						& "," 
						& rs_openTask["task_chooesd"][j] 
						& "," 
						& rs_openTask["cr_type_name"][j] 
						& "," 
						& rs_openTask["campus_name"][j] 
						& "," 
						& rs_openTask["task_week"][j] 
						& "," 
						& rs_openTask["task_period"][j]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_openTask["task_time"][j]), ",", "、", "all") 
						& "," 
						& replace(rs_openTask["task_place"][j], ",", "、", "all")
						& ", , , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_openTask["tch_id"][j] neq rs_openTask["sec_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["sec_name"][j], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["sec_id"][j], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_openTask["tch_id"][j] neq rs_openTask["ace_id"][j] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["ace_name"][j], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["ace_id"][j], row, 16);
			}

			if (rs_openTask["task_time"][j] neq "-") {
				
				for (i=1; i lte listLen(rs_openTask["task_time"][j], ","); i++) {
					
					timeCell = listGetAt(rs_openTask["task_time"][j], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 24 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_openTask["task_place"][j] neq "-" and listLen(rs_openTask["task_place"][j], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_openTask["task_place"][j], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>